<?php
require_once 'application/library/db/gcBasicDao.php';
require_once 'application/constant/dbConstant.php';
/**
 * 客户管理Dao
 * dao_T_AD_M_MEMBERDao
 * @author    Soochow University
 * @version   1.0
 * @exclude   作  成  日：2011/04/07
 */

class dao_T_AD_M_MEMBERDao extends gcBasicDao{

    protected $_table = "T_AD_M_MEMBERD";

    /**
     * 客户总数取得
     * @return  int     $intReturn
     */
    public function pbGetMemberSum(){
        $sql = "SELECT "
                    . "COUNT(A.MEMBER_NO) "
            . "FROM "
                    . " T_AD_M_MEMBER AS A "
            . "WHERE 1=1 ";
        $intReturn = $this->select($sql, null, dbReturnType::ONE);
        return $intReturn;
    }
    /**
     * 客户情报一览取得
     * @param   array   $arrParameter   检索条件
     * @param   int     $intSearchType  1-件数/2-数据
     * @return  array   $arrMemberList  客户情报一览
     */
    public function pbGetMemberData($arrParameter, $intSearchType){
        if ($intSearchType == seachType::COUNT_TYPE) {
            $sql = "SELECT "
                    . "COUNT(A.MEMBER_NO) "
            . "FROM "
                    . " T_AD_M_MEMBER AS A "
            . "WHERE 1=1 ";
        } else if ($intSearchType == seachType::SEARCH_TYPE){
            $sql = "SELECT "
                    . "A.MEMBER_NO, "
                    . "A.MEMBER_ID, "
                    . "A.CONTRACT_NO, "
                    . "IFNULL(A.MEMBER_NAME, '') AS MEMBER_NAME, "
                    . "IFNULL(A.SALE_TP, '') AS SALE_TP, "
                    . "IFNULL(A.PROV_CODE, '') AS PROV_CODE, "
                    . "CONCAT(A.CITY,A.DISTRICT) AS POSITION, "
                    . "IFNULL(A.TEL, '') AS TEL, "
                    . "IFNULL(A.FAX, '') AS FAX, "
                    . "IFNULL(A.MOBILE, '') AS MOBILE, "
                    . "IFNULL(A.QQ, '') AS QQ, "
                    . "IFNULL(A.MSN, '') AS MSN, "
                    . "IFNULL(A.ALI, '') AS ALI, "
                    . "IFNULL(A.EMAIL, '') AS EMAIL, "
                    . "A.STATUS, "
                    . "A.SCALE, "
                    . "IFNULL(A.HOMEPAGE_URL, '') AS HOMEPAGE_URL, "
                    . "IFNULL(A.LAST_ORDER_DATE, '') AS LAST_ORDER_DATE, "
                    . "A.ORDER_COUNT, "
                    . "IFNULL(A.PAYMENTKIND_TP1, '') AS PAYMENTKIND_TP1, "
                    . "IFNULL(A.ACCOUNT1, '') AS ACCOUNT1, "
                    . "IFNULL(A.PAYMENTKIND_TP2, '') AS PAYMENTKIND_TP2, "
                    . "IFNULL(A.ACCOUNT2, '') AS ACCOUNT2, "
                    . "IFNULL(A.PAYMENTKIND_TP3, '') AS PAYMENTKIND_TP3, "
                    . "IFNULL(A.ACCOUNT3, '') AS ACCOUNT3, "
                    . "IFNULL(A.CREATE_DATE, '') AS CREATE_DATE, "
                    . "A.DATA_VERSION "
            . "FROM "
                    . " T_AD_M_MEMBER AS A "
            . "WHERE 1=1 ";
        }
        //客户ID
        if ($arrParameter["MemberId"] != null && $arrParameter["MemberId"] != "") {
            $sql .= "AND A.MEMBER_ID LIKE CONCAT('%' , :MemberId , '%') ";
            $arrParameterDB["MemberId"] = $arrParameter["MemberId"];
        } else {}
        //契約番号
        if ($arrParameter["ContractNo"] != null && $arrParameter["ContractNo"] != "") {
            $sql .= "AND A.CONTRACT_NO LIKE CONCAT('%' , :ContractNo , '%') ";
            $arrParameterDB["ContractNo"] = $arrParameter["ContractNo"];
        } else {}
        //客户名
        if ($arrParameter["MemberName"] != null && $arrParameter["MemberName"] != "") {
            $sql .= "AND A.MEMBER_NAME LIKE CONCAT('%' , :MemberName , '%') ";
            $arrParameterDB["MemberName"] = $arrParameter["MemberName"];
        } else {}
        //销售区分 
        if ($arrParameter["SaleTp"] != null && $arrParameter["SaleTp"] != "") {
            $sql .= "AND A.SALE_TP = :SaleTp ";
            $arrParameterDB["SaleTp"] = $arrParameter["SaleTp"];
        } else {}
        //所在省份
        if ($arrParameter["ProvCode"] != null && $arrParameter["ProvCode"] != "") {
            $sql .= "AND A.PROV_CODE = :ProvCode ";
            $arrParameterDB["ProvCode"] = $arrParameter["ProvCode"];
        } else {}
        //详细地址
        if ($arrParameter["Position"] != null && $arrParameter["Position"] != "") {
            $sql .= "AND CONCAT_WS('', A.CITY, A.DISTRICT) LIKE CONCAT('%' , :Position , '%') ";
            $arrParameterDB["Position"] = $arrParameter["Position"];
        } else {}
        //客户状态
        if ($arrParameter["MemberStatus"] != null && $arrParameter["MemberStatus"] != "") {
            $sql .= "AND A.STATUS = :MemberStatus ";
            $arrParameterDB["MemberStatus"] = $arrParameter["MemberStatus"];
        } else {}
        //客户规模
        if ($arrParameter["MemberScale"] != null && $arrParameter["MemberScale"] != "") {
            $sql .= "AND A.SCALE IN ( ";
            $intCount = count($arrParameter["MemberScale"]);
            for ($i = 0; $i < $intCount; $i++) {
                if ($intCount != $i+1) {
                    $arrParameterDB["MemberScale" . $i] = $arrParameter["MemberScale"][$i];
                    $sql .= " :MemberScale" . $i . " ,";
                } else {
                    $arrParameterDB["MemberScale" . $i] = $arrParameter["MemberScale"][$i];
                    $sql .= " :MemberScale" . $i . " ) ";
                }
            }
        } else {}
        //客户登録日
        if ($arrParameter["CreateDateFrom"] != null && $arrParameter["CreateDateFrom"] != "") {
            $sql .= "AND DATE_FORMAT(A.CREATE_DATE, '%Y/%m/%d') >= DATE_FORMAT(:CreateDateFrom, '%Y/%m/%d') ";
            $arrParameterDB["CreateDateFrom"] = $arrParameter["CreateDateFrom"];
        } else {}
        if ($arrParameter["CreateDateTo"] != null && $arrParameter["CreateDateTo"] != "") {
            $sql .= "AND DATE_FORMAT(A.CREATE_DATE, '%Y/%m/%d') <= DATE_FORMAT(:CreateDateTo, '%Y/%m/%d') ";
            $arrParameterDB["CreateDateTo"] = $arrParameter["CreateDateTo"];
        } else {}
        //电话号码 
        if ($arrParameter["Tel"] != null && $arrParameter["Tel"] != "") {
            $sql .= "AND A.TEL LIKE CONCAT('%' , :Tel , '%') ";
            $arrParameterDB["Tel"] = $arrParameter["Tel"];
        } else {}
        //FAX号码 
        if ($arrParameter["Fax"] != null && $arrParameter["Fax"] != "") {
            $sql .= "AND A.FAX LIKE CONCAT('%' , :Fax , '%') ";
            $arrParameterDB["Fax"] = $arrParameter["Fax"];
        } else {}
        //手机号码 
        if ($arrParameter["Mobile"] != null && $arrParameter["Mobile"] != "") {
            $sql .= "AND A.MOBILE LIKE CONCAT('%' , :Mobile , '%') ";
            $arrParameterDB["Mobile"] = $arrParameter["Mobile"];
        } else {}
        //电子邮件
        if ($arrParameter["Email"] != null && $arrParameter["Email"] != "") {
            $sql .= "AND A.EMAIL LIKE CONCAT('%' , :Email , '%') ";
            $arrParameterDB["Email"] = $arrParameter["Email"];
        } else {}
        //交易回数
        if ($arrParameter["OrderCountFrom"] != null && $arrParameter["OrderCountFrom"] != "") {
            $sql .= "AND A.ORDER_COUNT >= :OrderCountFrom ";
            $arrParameterDB["OrderCountFrom"] = $arrParameter["OrderCountFrom"];
        } else {}
        if ($arrParameter["OrderCountTo"] != null && $arrParameter["OrderCountTo"] != "") {
            $sql .= "AND A.ORDER_COUNT <= :OrderCountTo ";
            $arrParameterDB["OrderCountTo"] = $arrParameter["OrderCountTo"];
        } else {}

        if ($intSearchType == seachType::COUNT_TYPE) {
            $arrMemberList = $this->select($sql, $arrParameterDB, dbReturnType::ONE);
            return $arrMemberList;
        } else if ($intSearchType == seachType::SEARCH_TYPE){
            if ($arrParameter["orderColumn"] != "" && $arrParameter["order"] != "") {
                $sql .= "ORDER BY " . $arrParameter["orderColumn"] . " " . $arrParameter["order"] . " ";
            } else {
                $sql .= "ORDER BY A.MEMBER_NO DESC ";
            }
            $sql .= "LIMIT " . $arrParameter["limit"] . " OFFSET " . $arrParameter["offset"];
            $arrMemberList = $this->select($sql, $arrParameterDB, dbReturnType::ALL);
            return $arrMemberList;
        }
    }

    /**
     * 客户情报取得
     * @param   string  $strMemberNo
     * @return  array   $arrReturn  客户情报
     */
    public function pbGetMemberInfo($strMemberNo){
        $sql = "SELECT "
                . "A.MEMBER_NO, "
                . "A.MEMBER_ID, "
                . "A.CONTRACT_NO, "
                . "IFNULL(A.MEMBER_NAME, '') AS MEMBER_NAME, "
                . "IFNULL(A.SALE_TP, '') AS SALE_TP, "
                . "IFNULL(A.PROV_CODE, '') AS PROV_CODE, "
                . "IFNULL(A.CITY, '') AS CITY, "
                . "IFNULL(A.DISTRICT, '') AS DISTRICT, "
                . "IFNULL(A.TEL, '') AS TEL, "
                . "IFNULL(A.FAX, '') AS FAX, "
                . "IFNULL(A.MOBILE, '') AS MOBILE, "
                . "IFNULL(A.QQ, '') AS QQ, "
                . "IFNULL(A.MSN, '') AS MSN, "
                . "IFNULL(A.ALI, '') AS ALI, "
                . "IFNULL(A.EMAIL, '') AS EMAIL, "
                . "A.STATUS, "
                . "A.SCALE, "
                . "IFNULL(A.HOMEPAGE_URL, '') AS HOMEPAGE_URL, "
                . "IFNULL(A.LAST_ORDER_DATE, '') AS LAST_ORDER_DATE, "
                . "A.ORDER_COUNT, "
                . "IFNULL(A.PAYMENTKIND_TP1, '') AS PAYMENTKIND_TP1, "
                . "IFNULL(A.ACCOUNT1, '') AS ACCOUNT1, "
                . "IFNULL(A.PAYMENTKIND_TP2, '') AS PAYMENTKIND_TP2, "
                . "IFNULL(A.ACCOUNT2, '') AS ACCOUNT2, "
                . "IFNULL(A.PAYMENTKIND_TP3, '') AS PAYMENTKIND_TP3, "
                . "IFNULL(A.ACCOUNT3, '') AS ACCOUNT3, "
                . "IFNULL(A.CREATE_DATE, '') AS CREATE_DATE, "
                . "A.DATA_VERSION "
        . "FROM "
                . " T_AD_M_MEMBER AS A "
        . "WHERE A.MEMBER_NO = :MemberNo ";
        $arrParameter = array("MemberNo" => $strMemberNo);
        $arrReturn = $this->select($sql, $arrParameter, dbReturnType::ROW);

        return $arrReturn;
    }

    /**
     * 客户情报编辑保存
     * @param   array   $arrParameter
     * @return  string  $strReturn
     */
    public function pbUpdateMemberInfo($arrParameter){
        $sql = "UPDATE T_AD_M_MEMBER SET "
                . "MEMBER_ID = :MemberId, "
                . "CONTRACT_NO = :ContractNo, "
                . "MEMBER_NAME = :MemberName, "
                . "SALE_TP = :SaleTp, "
                . "PROV_CODE = :ProvCode, "
                . "CITY = :City, "
                . "DISTRICT = :District, "
                . "TEL = :Tel, "
                . "FAX = :Fax, "
                . "MOBILE = :Mobile, "
                . "QQ = :QQ, "
                . "MSN = :MSN, "
                . "ALI = :Ali, "
                . "EMAIL = :Email, "
                . "STATUS = :MemberStatus, "
                . "SCALE = :MemberScale, "
                . "HOMEPAGE_URL = :Homepage, "
                . "PAYMENTKIND_TP1 = :PaymentTp1, "
                . "ACCOUNT1 = :Account1, "
                . "PAYMENTKIND_TP2 = :PaymentTp2, "
                . "ACCOUNT2 = :Account2, "
                . "PAYMENTKIND_TP3 = :PaymentTp3, "
                . "ACCOUNT3 = :Account3, "
                . "MODIFY_USER_NO = :UserNo,"
                . "MODIFY_DATE = NOW(), "
                . "DATA_VERSION = DATA_VERSION + 1 "
        . "WHERE MEMBER_NO = :MemberNo "
                ."AND DATA_VERSION = :DataVersion ";
        $strReturn = $this->update($sql, $arrParameter);
        return $strReturn;
    }
    
    /**
     * 客户情报新规保存
     * @param   array   $arrParameter
     * @return  string  $strReturn
     */
    public function pbInsertMemberInfo($arrParameter){
        $sql = "INSERT INTO T_AD_M_MEMBER ( "
                . "MEMBER_ID, "
                . "CONTRACT_NO, "
                . "MEMBER_NAME, "
                . "SALE_TP, "
                . "PROV_CODE, "
                . "CITY, "
                . "DISTRICT, "
                . "TEL, "
                . "FAX, "
                . "MOBILE, "
                . "QQ, "
                . "MSN, "
                . "ALI, "
                . "EMAIL, "
                . "STATUS, "
                . "SCALE, "
                . "HOMEPAGE_URL, "
                . "PAYMENTKIND_TP1, "
                . "ACCOUNT1, "
                . "PAYMENTKIND_TP2, "
                . "ACCOUNT2, "
                . "PAYMENTKIND_TP3, "
                . "ACCOUNT3, "
                . "CREATE_USER_NO,"
                . "CREATE_DATE "
            . ") VALUES ( "
                . ":MemberId , "
                . ":ContractNo , "
                . ":MemberName , "
                . ":SaleTp , "
                . ":ProvCode , "
                . ":City , "
                . ":District , "
                . ":Tel , "
                . ":Fax , "
                . ":Mobile , "
                . ":QQ , "
                . ":MSN , "
                . ":Ali , "
                . ":Email , "
                . ":MemberStatus , "
                . ":MemberScale , "
                . ":Homepage , "
                . ":PaymentTp1, "
                . ":Account1, "
                . ":PaymentTp2, "
                . ":Account2, "
                . ":PaymentTp3, "
                . ":Account3, "
                . ":UserNo , "
                . "NOW()) ";
        $strReturn = $this->insert($sql, $arrParameter);
        return $strReturn;
    }

    /**
     * 客户情报新规保存
     * @param   array   $arrParameter
     * @return  string  $strReturn
     */
    public function pbDeletetMemberInfo($arrParameter){
        $sql = "DELETE FROM T_AD_M_MEMBER "
        . "WHERE MEMBER_NO = :MemberNo "
                ."AND DATA_VERSION = :DataVersion ";
        $strReturn = $this->delete($sql, $arrParameter);
        return $strReturn;
    }

    /**
     * 客户情报取得
     * @param   array   $arrParameter
     * @return  array   $arrReturn  客户情报
     */
    public function pbGetMemberLoginInfo($arrParameter){
        $sql = "SELECT "
                . "A.MEMBER_NO, "
                . "A.MEMBER_ID, "
                . "A.MEMBER_NAME, "
                . "A.LOGO "
        . "FROM "
                . " T_AD_M_MEMBER AS A "
        . "WHERE A.MEMBER_ID = :UserId "
            . "AND A.PASSWORD = :UserPassword ";
        $arrReturn = $this->select($sql, $arrParameter, dbReturnType::ROW);
        return $arrReturn;
    }
}